package com.ifd.iview.as400.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.ifd.iview.common.ServiceLocatorLRU;
import com.ifd.iview.model.UserManager;
import com.ifd.iview.model.UserVO;

public class UserManager400 implements UserManager {
	
	private Logger logger = Logger.getLogger(UserManager400.class);
	
	@Override
	public int delete(UserVO user) {
		Connection conn = null;
		Statement s = null;
		try {
			conn = ServiceLocatorLRU.getJDBCConnection();
			conn.setAutoCommit(false);
			s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			return s.executeUpdate("DELETE FROM IVUSRPRFP WHERE LCASE(UPUSRNAM) = '" + user.getUsername().toLowerCase() + "'");	
		} catch (Exception e) {
		} finally {
			try {
				s.close();
			} catch (Exception e2) {
			}
			if(conn!=null)
				ServiceLocatorLRU.returnJDBCConnection(conn);
		}
		return 0;
	}

	@Override
	public int update(UserVO user) {
		Connection conn = null;
		Statement s = null;
		try {
			conn = ServiceLocatorLRU.getJDBCConnection();
			conn.setAutoCommit(false);
			s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			return s.executeUpdate("UPDATE IVUSRPRFP " +
			                 "SET (UPUSREML, UPPASWRD, UPSTATUS, UPEXPDAT, UPENTDAT, UPDOBDAT, UPALSNAM) = (" +
					         "'" + user.getEmailAddress().trim() + "', " +
					         "'" + user.getPassword().trim() + "', " +
					         "'" + user.getStatus().trim() + "', " +
					         user.getExpirationDate() + ", " +
					         user.getEntryDate() + ", " +
					         user.getDateOfBirth() + ", " +
					         "'" + user.getAliasName().trim() + "'" +  
					         ") WHERE LCASE(UPUSRNAM) = '" + user.getUsername().toLowerCase() + "'");	
		} catch (Exception e) {
		} finally {
			try {
				s.close();
			} catch (Exception e2) {
			}
			if(conn!=null)
				ServiceLocatorLRU.returnJDBCConnection(conn);
		}
		return 0;
	}

	@Override
	public int add(UserVO user) {
		Connection conn = null;
		Statement s = null;
		try {
			conn = ServiceLocatorLRU.getJDBCConnection();
			conn.setAutoCommit(false);
			s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			return s.executeUpdate("INSERT INTO IVUSRPRFP " +
			                 "(UPUSRNAM, UPUSREML, UPPASWRD, UPSTATUS, UPEXPDAT, UPENTDAT, UPDOBDAT, UPALSNAM) VALUES(" +
			                 "'" + user.getUsername().toUpperCase().trim() + "', " +
					         "'" + user.getEmailAddress().trim() + "', " +
					         "'" + user.getPassword().trim() + "', " +
					         "'" + user.getStatus().trim() + "', " +
					         user.getExpirationDate() + ", " +
					         user.getEntryDate() + ", " +
					         user.getDateOfBirth() + ", " +
					         "'" + user.getAliasName().trim() + "'" +  
					         ")");	
		} catch (Exception e) {
		} finally {
			try {
				s.close();
			} catch (Exception e2) {
			}
			if(conn!=null)
				ServiceLocatorLRU.returnJDBCConnection(conn);
		}
		return 0;
	}

	@Override
	public List getAll() {
		ArrayList<UserVO> list = new ArrayList<UserVO>();
		Connection conn = null;
		Statement s = null;
		ResultSet rs = null;
		
		try {
			conn = ServiceLocatorLRU.getJDBCConnection();
			conn.setAutoCommit(false);
			s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = s.executeQuery("SELECT * FROM IVUSRPRFP");
			while(rs.next()){
				UserVO user = new UserVO();
				String username = rs.getString(1);
				String password = rs.getString(2);
				String status = rs.getString(3);
				long expirationDate = rs.getLong(4);
				long entryDate = rs.getLong(5);
				long dateOfBirth = rs.getLong(6);
				String aliasName = rs.getString(7);
				String emailAddress = rs.getString(8);
				user.setAliasName(aliasName);
				user.setDateOfBirth(dateOfBirth);
				user.setEmailAddress(emailAddress);
				user.setEntryDate(entryDate);
				user.setExpirationDate(expirationDate);
				user.setPassword(password);
				user.setStatus(status);
				user.setUsername(username);
				list.add(user);
			};
		} catch (Exception e) {
			logger.error("Error when load all users", e);
		} finally {
			try {
				rs.close();
				s.close();
			} catch (Exception e2) {
			}
			if(conn!=null)
				ServiceLocatorLRU.returnJDBCConnection(conn);
		}
		return list;
	}

	@Override
	public UserVO findBy(String pUsername, String pPassword) {
		Connection conn = null;
		Statement s = null;
		ResultSet rs = null;
		UserVO user = null;
		try {
			conn = ServiceLocatorLRU.getJDBCConnection();
			conn.setAutoCommit(false);
			s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = s.executeQuery("SELECT * FROM IVUSRPRFP WHERE LCASE(UPUSRNAM) = '" + pUsername.trim().toLowerCase() + 
					            "' AND UPPASWRD = '" + pPassword.trim() + "'");
			if(rs.next()){
				user = new UserVO();
				String username = rs.getString(1);
				String password = rs.getString(2);
				String status = rs.getString(3);
				long expirationDate = rs.getLong(4);
				long entryDate = rs.getLong(5);
				long dateOfBirth = rs.getLong(6);
				String aliasName = rs.getString(7);
				String emailAddress = rs.getString(8);
				user.setAliasName(aliasName);
				user.setDateOfBirth(dateOfBirth);
				user.setEmailAddress(emailAddress);
				user.setEntryDate(entryDate);
				user.setExpirationDate(expirationDate);
				user.setPassword(password);
				user.setStatus(status);
				user.setUsername(username);
			};	
		} catch (Exception e) {
			logger.error("User " + pUsername + " does not exist", e);
		} finally {
			try {
				rs.close();
				s.close();
			} catch (Exception e2) {
			}
			if(conn!=null)
				ServiceLocatorLRU.returnJDBCConnection(conn);
		}
		return user;
	}

	@Override
	public UserVO findBy(String userName) {
		Connection conn = null;
		Statement s = null;
		ResultSet rs = null;
		UserVO user = null;
		try {
			conn = ServiceLocatorLRU.getJDBCConnection();
			conn.setAutoCommit(false);
			s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			String sql = "SELECT * FROM IVUSRPRFP WHERE LCASE(UPUSRNAM) = '" + userName.trim().toLowerCase().concat("'");
			rs = s.executeQuery(sql);
			if(rs.first()){
				user = new UserVO();
				String username = rs.getString(1);
				String password = rs.getString(2);
				String status = rs.getString(3);
				long expirationDate = rs.getLong(4);
				long entryDate = rs.getLong(5);
				long dateOfBirth = rs.getLong(6);
				String aliasName = rs.getString(7);
				String emailAddress = rs.getString(8);
				user.setAliasName(aliasName);
				user.setDateOfBirth(dateOfBirth);
				user.setEmailAddress(emailAddress);
				user.setEntryDate(entryDate);
				user.setExpirationDate(expirationDate);
				user.setPassword(password);
				user.setStatus(status);
				user.setUsername(username);
			};	
		} catch (Exception e) {
			logger.error("User " + userName + " does not exist", e);
		} finally {
			try {
				rs.close();
				s.close();
			} catch (Exception e2) {
			}
			if(conn!=null)
				ServiceLocatorLRU.returnJDBCConnection(conn);
		}
		return user;
	}
}
